
alter   function f_finmsdept(
    @bnyear integer,
    @bnmonth integer,
    @enyear integer,
    @enmonth integer,
    @bsubcode varchar(40) ,
    @esubcode varchar(40) ,
    @nsublevels integer,
    @esublevels integer,
    @departmentid integer,
    @nmoneyid integer,
    @nfilid integer
)
returns @local_tab table(
    nyear integer,
    nmonth integer,
    subid integer,
    moneyid integer,
    bmbala decimal(18,2),
    bysumdebit decimal(18,2),
    bysumcredit decimal(18,2),
    tmsumdebit decimal(18,2),
    tmsumcredit decimal(18,2),
    tmsum decimal(18,2)
)
as

begin

  declare @tempsublevel integer

  declare @fbmbala     decimal(18,2)
  declare @fbysumdebit decimal(18,2)
  declare @fbysumcredit decimal(18,2)
  declare @ftmsumdebit decimal(18,2)
  declare @ftmsumcredit decimal(18,2)
  declare @ftmsum      decimal(18,2)

  declare
    @nyear integer,
    @nmonth integer,
    @subid integer,
    @moneyid integer,
    @bmbala decimal(18,2),
    @bysumdebit decimal(18,2),
    @bysumcredit decimal(18,2),
    @tmsumdebit decimal(18,2),
    @tmsumcredit decimal(18,2),
    @tmsum decimal(18,2)

  set @moneyid=@nmoneyid

  if (@bsubcode is null or @bsubcode='')
    set @bsubcode='0'
  if (@esubcode is null or @esubcode='')
    set @esubcode='9'
  
  if (@nmoneyid=-99)
  begin

    insert into @local_tab(     
      nyear,
      nmonth,
      subid,
      moneyid,
      bmbala,
      bysumdebit,
      bysumcredit,
      tmsumdebit,
      tmsumcredit,
      tmsum
    ) 
    select d.nyear,d.nmonth,s.subjectid,@moneyid as moneyid,
      (s.isdebit*2-1)*sum(d.bmbala),sum(bysumdebit),sum(bysumcredit),
      sum(tmsumdebit),sum(tmsumcredit),(s.isdebit*2-1)*sum(tmsum)
    from dbo.f_finmsdeptdtl (
      @bnyear ,
      @bnmonth,
      @enyear ,
      @enmonth ,
      @nsublevels ,
      @departmentid,
      @nfilid) d
    inner join tfd_subject s on d.subcode=s.subcode
    group by d.nyear,d.nmonth,s.subjectid,s.subcode,s.sublevel,s.isdebit
    having s.subcode>=@bsubcode and s.subcode<=@esubcode+'z' and
      s.sublevel>=@nsublevels and s.sublevel<=@esublevels
  end
  else
  begin
    declare local_cursor cursor local
    for select d.nyear,d.nmonth,s.subjectid,d.moneyid,s.sublevel,
        (s.isdebit*2-1)*sum(d.bmbala),sum(bysumdebit),sum(bysumcredit),
        sum(tmsumdebit),sum(tmsumcredit),(s.isdebit*2-1)*sum(tmsum),
        (s.isdebit*2-1)*sum(d.fbmbala),sum(fbysumdebit),sum(fbysumcredit),
        sum(ftmsumdebit),sum(ftmsumcredit),(s.isdebit*2-1)*sum(ftmsum)
        from dbo.f_finmsdeptdtl (
          @bnyear ,
          @bnmonth,
          @enyear ,
          @enmonth ,
          @nsublevels ,
          @departmentid,
          @nfilid) d
        inner join tfd_subject s on d.subcode=s.subcode
        group by d.nyear,d.nmonth,s.subjectid,s.subcode,d.moneyid,s.sublevel,s.isdebit
        having s.subcode>=@bsubcode and s.subcode<=@esubcode+'z' and
          s.sublevel>=@nsublevels and s.sublevel<=@esublevels and d.moneyid=@nmoneyid
 
    open local_cursor
    fetch next from local_cursor into @nyear,@nmonth,@subid,@moneyid,@tempsublevel,
          @bmbala,@bysumdebit,@bysumcredit,
          @tmsumdebit,@tmsumcredit,@tmsum,
          @fbmbala,@fbysumdebit,@fbysumcredit,
          @ftmsumdebit,@ftmsumcredit,@ftmsum
    while @@fetch_status = 0
    begin
      set @moneyid=@nmoneyid         
      insert into @local_tab(
        nyear,
        nmonth,
        subid,
        moneyid,
        bmbala,
        bysumdebit,
        bysumcredit,
        tmsumdebit,
        tmsumcredit,
        tmsum
      ) values (
        @nyear,
        @nmonth,
        @subid,
        @moneyid,
        @bmbala,
        @bysumdebit,
        @bysumcredit,
        @tmsumdebit,
        @tmsumcredit,
        @tmsum
      )
      if (@moneyid<>0)
      begin
        set @moneyid=-@nmoneyid

        set @bmbala      =@fbmbala
        set @bysumdebit  =@fbysumdebit
        set @bysumcredit =@fbysumcredit
        set @tmsumdebit  =@ftmsumdebit
        set @tmsumcredit =@ftmsumcredit
        set @tmsum       =@ftmsum
        insert into @local_tab(
          nyear,
          nmonth,
          subid,
          moneyid,
          bmbala,
          bysumdebit,
          bysumcredit,
          tmsumdebit,
          tmsumcredit,
          tmsum
        ) values (
          @nyear,
          @nmonth,
          @subid,
          @moneyid,
          @bmbala,
          @bysumdebit,
          @bysumcredit,
          @tmsumdebit,
          @tmsumcredit,
          @tmsum
        )
      end
      fetch next from local_cursor into @nyear,@nmonth,@subid,@moneyid,@tempsublevel,
          @bmbala,@bysumdebit,@bysumcredit,
          @tmsumdebit,@tmsumcredit,@tmsum,
          @fbmbala,@fbysumdebit,@fbysumcredit,
          @ftmsumdebit,@ftmsumcredit,@ftmsum
    end
    close local_cursor
    deallocate local_cursor      
  end
  
  return
end





